package net.wanho.commons.util;


import net.wanho.commons.exception.DataAccessException;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.Converter;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.log4j.Logger;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


public class JDBCTemplate {
    private static final Logger LOGGER = Logger.getLogger(JDBCTemplate.class);

    public int insert(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行插入的SQL==================>" + sql);
            LOGGER.debug("执行插入SQL参数================>" + ArrayUtils.toString(params));
            return new QueryRunner().insert(JDBCUtil.getConnection(), sql, new ScalarHandler<Long>(), params).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行插入异常:" + e.getMessage());
            throw new DataAccessException("数据保存异常");
        }
    }

    /**
     * 批量更新
     * @param sql  更新sql
     * @param params  二维数组  参数
     * @return
     * @throws DataAccessException
     */
    public boolean updateBatch(String sql, Object[]... params) throws DataAccessException {
        try {
            LOGGER.debug("执行updateBatch的SQL==================>" + sql);
            LOGGER.debug("执行updateBatchSQL参数================>" + ArrayUtils.toString(params));
            new QueryRunner().batch(JDBCUtil.getConnection(), sql, params);
            return true ;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行updateBatch异常:" + e.getMessage());
            throw new DataAccessException("数据updateBatch更新异常");
        }
    }
    public boolean update(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行修改的SQL==================>" + sql);
            LOGGER.debug("执行修改SQL参数================>" + ArrayUtils.toString(params));
            return new QueryRunner().update(JDBCUtil.getConnection(), sql, params)>0;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行修改异常:" + e.getMessage());
            throw new DataAccessException("数据更新异常");
        }
    }

    public boolean delete(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行删除的SQL==================>" + sql);
            LOGGER.debug("执行删除SQL参数================>" + ArrayUtils.toString(params));
            return  new QueryRunner().update(JDBCUtil.getConnection(), sql, params)>0;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行删除异常:" + e.getMessage());
            throw new DataAccessException("数据删除异常");
        }
    }

    /**
     * 查询 返回多行数据
     *
     * @param sql
     * @param cls
     * @param params
     * @param <T>
     * @return
     * @throws DataAccessException
     */
    public <T> T queryForList(String sql, Class cls, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行查询queryForList的SQL==================>" + sql);
            LOGGER.debug("执行查询queryForListS 参数================>" + ArrayUtils.toString(params));
            List<Map<String, Object>> mapList = new QueryRunner().query(JDBCUtil.getConnection(), sql, new MapListHandler(), params);
            //查询没有结果集数据
            if (mapList==null || mapList.isEmpty()){
                // 返回 空 集合
                return (T) new ArrayList();
            }
            List list = new ArrayList() ;
            //循环 list
            mapList.forEach(map->{
                try {
                    //通过反射 new pojo 的实例
                    Object instance = cls.newInstance();
                    //处理日期数据类型
                    ConvertUtils.register(new Converter() {
                        public Object convert(Class type, Object value) {
                            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                            try {
                                return simpleDateFormat.parse(value.toString());
                            } catch (ParseException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                            return null;
                        }
                    }, Date.class);
                    //把 map 同名key  自动赋值给 pojo
                    BeanUtils.populate(instance,map);
                    // 把pojo 添加的集合中
                    list.add(instance) ;
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            });
            return (T) list;
        } catch (Exception e) {
            e.printStackTrace();
			LOGGER.error("执行查询queryForList异常:" + e.getMessage());
            throw new DataAccessException("数据查询异常");
        }
    }


	/**
	 * 查询返回 单行数据
	 * @param sql
	 * @param cls
	 * @param params
	 * @param <T>
	 * @return
	 * @throws DataAccessException
	 */
	public <T> T queryForObject(String sql, Class cls, Object... params) throws DataAccessException {
		try {
			LOGGER.debug("执行查询queryForObject的SQL==================>" + sql);
			LOGGER.debug("执行查询queryForObject   参数================>" + ArrayUtils.toString(params));
            Map<String, Object> map = new QueryRunner().query(JDBCUtil.getConnection(), sql, new MapHandler(), params);
            //查询结果集为空  无需封装pojo
            if (map==null || map.isEmpty()){
                return null ;
            }
            //通过反射 new pojo 的实例
            Object instance = cls.newInstance();
            //处理日期数据类型
            ConvertUtils.register(new Converter() {
                public Object convert(Class type, Object value) {
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    try {
                        return simpleDateFormat.parse(value.toString());
                    } catch (ParseException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    return null;
                }
            }, Date.class);
            //把 map 同名key  自动赋值给 pojo
            BeanUtils.populate(instance,map);
            return (T) instance;
        } catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("queryForObject异常:" + e.getMessage());
			throw new DataAccessException("数据查询异常");
		}
	}


	/**
	 * 查询 满足条件的总记录数
	 * @param sql
	 * @param params
	 * @return
	 * @throws DataAccessException
	 */
	public Long queryForLong(String sql, Object... params) throws DataAccessException {
		try {
			LOGGER.debug("执行查询queryForInt的SQL==================>" + sql);
			LOGGER.debug("执行查询queryForLong   参数================>" + ArrayUtils.toString(params));
            Object query = new QueryRunner().query(JDBCUtil.getConnection(), sql, new ScalarHandler<>(), params);
            if (query instanceof Long){
                return (Long) query;
            }
            if (query instanceof  BigDecimal){
                BigDecimal decimal = (BigDecimal) query;
                return decimal.longValue() ;
            }
            return 0L ;
        } catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("执行查询queryForLong异常:" + e.getMessage());
			throw new DataAccessException("数据查询异常");
		}
	}

    public Map<String,Object> queryForMap(String sql,  Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行查询queryForMap的SQL==================>" + sql);
            LOGGER.debug("执行查询queryForMap 参数================>" + ArrayUtils.toString(params));
            return new QueryRunner().query(JDBCUtil.getConnection(), sql, new MapHandler(), params);
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.error("queryForMap异常:" + e.getMessage());
            throw new DataAccessException("数据查询异常");
        }
    }

    public List<Map<String,Object>> queryForMapList(String sql,  Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行查询queryForMapList的SQL==================>" + sql);
            LOGGER.debug("执行查询queryForMapList 参数================>" + ArrayUtils.toString(params));
            return new QueryRunner().query(JDBCUtil.getConnection(), sql, new MapListHandler(), params);
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.error("queryForMapList异常:" + e.getMessage());
            throw new DataAccessException("数据查询异常");
        }
    }
}
